package pCloud.service;

import java.net.InetSocketAddress;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import pCloud.message.DatabaseReply;
import pCloud.message.DatabaseRequest;
import pCloud.server.DatabaseHandle;

public class SQLDatabaseHandle implements DatabaseHandle {

	String databaseId, username, password, driver;

	Connection connection;

	/*
	 * Initialize the connection to database.
	 * 
	 * @see pCloud.server.DatabaseHandle#init()
	 */
	@Override
	public void init(String driver, String id, String username, String password)
			throws InstantiationException, IllegalAccessException,
			ClassNotFoundException, SQLException {
		this.driver = driver;
		this.databaseId = id;
		this.username = username;
		this.password = password;
		Class.forName(driver).newInstance();
		this.connection = DriverManager.getConnection(this.databaseId,
				username, password);
	}

	public void init(Connection connection) {
		this.connection = connection;
	}

	@Override
	public DatabaseReply evaluateRequest(String joiningColumn,
			DatabaseRequest request) throws Exception {
		String query = request.getQuery();
		Statement stmt = this.connection.createStatement();
		ResultSet rs = stmt.executeQuery(query);

		return constructReplyMessage(joiningColumn, rs);
	}

	/**
	 * Going through the result set and fill in the reply with all the result
	 * data.
	 * 
	 * This makes sure that the reply is indexed by the joining column. Each of
	 * that value is mapped to an array of other values that are in the same
	 * order as the columnId array.
	 */
	private DatabaseReply constructReplyMessage(String joiningColumnId,
			ResultSet rs) throws Exception {

		// If no joiningColumnId specified, use the first column as the key.
		// Note this will not result in data being joined at the proxy, since
		// the original joiningColumnId is still null and the proxy will not
		// perform joining.
		DatabaseReply reply = new DatabaseReply(joiningColumnId);

		int columnCount = rs.getMetaData().getColumnCount();
		String[] columnLabels = new String[columnCount];

		ArrayList[] values = new ArrayList[columnCount];

		String columnId;
		int joiningColIndex = -1;

		for (int i = 0; i < columnCount; i++) {
			columnId = rs.getMetaData().getColumnName(i + 1);
			if (!columnId.equals(joiningColumnId))
				reply.addColumnId(columnId);
			else
				joiningColIndex = i + 1;
		}

		Object keyValue;
		// System.out.println("******************");
		int counter = 0;
		while (rs.next()) {
			// first, add the value of the joining column
			if (joiningColumnId != null)
				keyValue = rs.getObject(joiningColIndex);
			else
				keyValue = counter;
			counter++;
			reply.addKeyValue(keyValue);
			// System.out.print("Value = "+keyValue+"\t");
			for (int i = 0; i < columnCount; i++) {
				if ((i + 1) != joiningColIndex) {
					reply.addValueToRow(keyValue, rs.getObject(i + 1));
					// System.out.print(rs.getObject(i+1)+"\t");
				}
			}
			// System.out.println();
		}
		return reply;
	}

	@Override
	public Connection getConnection() {
		return this.connection;
	}

}
